package com.rxh.util;

import com.rxh.pojo.Excel;
import com.rxh.pojo.finance.FinanceDrawing;
import com.rxh.pojo.sys.SysConstant;
import com.rxh.utils.SystemConstant;
import com.rxh.vo.VoCoreOrder;
import com.rxh.vo.VoCoreOrderExport;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: 陈俊雄
 * Date: 2018/4/2
 * Time: 9:53
 * Project: Management
 * Package: com.rxh.utils
 */
public class ExcelUtils {
    private static final String SUFFIX_CSV = ".csv";
    private static final String SUFFIX_XLS = ".xls";
    private static final String SUFFIX_XLSX = ".xlsx";

    // private static SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm");

    public static Excel getWorkbooks(MultipartFile multipartFile) throws IOException {
        if (multipartFile.getOriginalFilename().endsWith(SUFFIX_XLS)) {
            return new Excel(multipartFile.getOriginalFilename(), new HSSFWorkbook(multipartFile.getInputStream()));
        } else if (multipartFile.getOriginalFilename().endsWith(SUFFIX_XLSX)) {
            return new Excel(multipartFile.getOriginalFilename(), new XSSFWorkbook(multipartFile.getInputStream()));
        } else if (multipartFile.getOriginalFilename().endsWith(SUFFIX_CSV)) {
            Reader reader = new InputStreamReader(multipartFile.getInputStream());
            return new Excel(multipartFile.getOriginalFilename(), new CSVParser(reader, CSVFormat.DEFAULT));
        }
        return null;
    }

    public static void exportOrderInfoToExcel(HttpServletResponse response, List<VoCoreOrderExport> orderExports, Map<String, Map<String, SysConstant>> constants) throws IOException {
        String[] SHEET_TITLES = {"订单信息", "通道信息", "变更信息", "客户信息"};
        String[][] ROW_TITLES = {
                {"平台订单号", "商户号", "域名", "商户订单号", "银行流水号", "支付方式", "支付类型", "收单账户", "币种", "订单金额", "交易汇率", "订单人民币", "商户扣率", "交易手续费人民币", "交易时间", "结果", "IP"},
                {"平台订单号", "通道金额", "通道币种", "通道扣率", "通道手续费", "通道汇率", "通道人民币金额", "通道交易编号"},
                {"平台订单号", "异常金额", "异常币种", "异常人民币金额", "异常状态", "清算金额", "清算币种", "清算人民币金额", "清算时间", "清算状态"},
                {"平台订单号", "客户姓名", "电话", "地址", "邮编", "邮箱", "快递公司", "快递单号", "发货时间"}};
        int cellRangeAddressCount = 0;
        // 系统常量：银行状态
        Map<String, SysConstant> bankStatus = constants.get(SystemConstant.BANK_STATUS);
        // 系统常量：异常状态
        Map<String, SysConstant> exceptionStatus = constants.get(SystemConstant.EXCEPTION_STATUS);
        // 新建excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/MM/dd HH:mm"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        for (int i = 0; i < SHEET_TITLES.length; i++) {
            sheet = workbook.createSheet(SHEET_TITLES[i]);
            row = sheet.createRow(0);
            for (int j = 0; j < ROW_TITLES[i].length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(ROW_TITLES[i][j]);
                cell.setCellStyle(headerCellStyle);
                if (j == 0) {
                    sheet.setColumnWidth(j, ROW_TITLES[i][j].getBytes().length * 256 + 1024 * 3);
                } else {
                    sheet.setColumnWidth(j, ROW_TITLES[i][j].getBytes().length * 256 + 1024);
                }
            }
        }
        for (int i = 0; i < orderExports.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderExports.get(i).getId()));
            cell.setCellStyle(cellSetString);

            cell = row.createCell(1);
            cell.setCellValue(orderExports.get(i).getMerId());

            cell = row.createCell(2);
            cell.setCellValue(orderExports.get(i).getMerchantWebSite().getSiteUrl());

            cell = row.createCell(3);
            cell.setCellValue(orderExports.get(i).getMerOrderId());
            cell.setCellStyle(cellSetString);

            cell = row.createCell(4);
            cell.setCellValue(orderExports.get(i).getBankOrderId());
            cell.setCellStyle(cellSetString);

            cell = row.createCell(5);
            cell.setCellValue(orderExports.get(i).getPayMode());

            cell = row.createCell(6);
            cell.setCellValue(orderExports.get(i).getPayType());

            cell = row.createCell(7);
            cell.setCellValue(orderExports.get(i).getAcquirerAccount().getName());

            cell = row.createCell(8);
            cell.setCellValue(orderExports.get(i).getCurrency());

            cell = row.createCell(9);
            cell.setCellValue(orderExports.get(i).getAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);

            cell = row.createCell(10);
            cell.setCellValue(orderExports.get(i).getRate().floatValue());
            cell.setCellStyle(cellNumFormatFourDecimalPlaces);

            cell = row.createCell(11);
            cell.setCellValue(String.valueOf(orderExports.get(i).getRmbAmount()));
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);

            cell = row.createCell(12);
            cell.setCellValue(orderExports.get(i).getMerchantRate().floatValue());
            cell.setCellStyle(cellNumFormatFourDecimalPlaces);

            cell = row.createCell(13);
            cell.setCellValue(String.valueOf(orderExports.get(i).getMerchantOrderRmb().getSourceRmbDeduct()));
            cell.setCellStyle(cellNumFormatFourDecimalPlaces);

            cell = row.createCell(14);
            cell.setCellValue(orderExports.get(i).getTradeTime());
            cell.setCellStyle(cellDateFormat);

            cell = row.createCell(15);
            cell.setCellValue(bankStatus.get(String.valueOf(orderExports.get(i).getBankStatus())).getName());

            cell = row.createCell(16);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getIp());

            sheet = workbook.getSheetAt(1);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderExports.get(i).getId()));
            cell.setCellStyle(cellSetString);

            cell = row.createCell(1);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getChannelAmount().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);

            cell = row.createCell(2);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getChannelCurrency());

            cell = row.createCell(3);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getChannelDeduct().floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);

            cell = row.createCell(4);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getChannelFeeRate().floatValue());
            cell.setCellStyle(cellNumFormatFourDecimalPlaces);

            cell = row.createCell(5);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getChannelTransRate().floatValue());
            cell.setCellStyle(cellNumFormatFourDecimalPlaces);

            cell = row.createCell(6);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getChannelAmount().multiply(orderExports.get(i).getChannelOrderInfo().getChannelSettleRate()).floatValue());
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);

            cell = row.createCell(7);
            cell.setCellValue(orderExports.get(i).getChannelOrderInfo().getMerchantId());
            cell.setCellStyle(cellSetString);

            sheet = workbook.getSheetAt(2);

            row = sheet.createRow(i + 1 + cellRangeAddressCount);
            if (orderExports.get(i).getOrderChanges() != null && orderExports.get(i).getOrderChanges().size() > 1) {
                sheet.addMergedRegion(new CellRangeAddress(i + 1 + cellRangeAddressCount, i + orderExports.get(i).getOrderChanges().size() + cellRangeAddressCount, 0, 0));
                sheet.addMergedRegion(new CellRangeAddress(i + 1 + cellRangeAddressCount, i + orderExports.get(i).getOrderChanges().size() + cellRangeAddressCount, 5, 5));
                sheet.addMergedRegion(new CellRangeAddress(i + 1 + cellRangeAddressCount, i + orderExports.get(i).getOrderChanges().size() + cellRangeAddressCount, 6, 6));
                sheet.addMergedRegion(new CellRangeAddress(i + 1 + cellRangeAddressCount, i + orderExports.get(i).getOrderChanges().size() + cellRangeAddressCount, 7, 7));
                sheet.addMergedRegion(new CellRangeAddress(i + 1 + cellRangeAddressCount, i + orderExports.get(i).getOrderChanges().size() + cellRangeAddressCount, 8, 8));
                sheet.addMergedRegion(new CellRangeAddress(i + 1 + cellRangeAddressCount, i + orderExports.get(i).getOrderChanges().size() + cellRangeAddressCount, 9, 9));
                cellRangeAddressCount += orderExports.get(i).getOrderChanges().size() - 1;
            }
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderExports.get(i).getId()));
            cellSetString.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            cell.setCellStyle(cellSetString);

            cell = row.createCell(5);
            cell.setCellValue(orderExports.get(i).getAmount()
                    .subtract(orderExports.get(i).getMerchantOrder().getDeduct())
                    .subtract(orderExports.get(i).getMerchantOrder().getBond())
                    .floatValue());
            cell.setCellStyle(cellVerticalCenter);
            cellNumFormatTwoDecimalPlaces.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            cell.setCellStyle(cellNumFormatTwoDecimalPlaces);

            cell = row.createCell(6);
            cellSetString.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            cell.setCellValue(orderExports.get(i).getCurrency());

            if (orderExports.get(i).getRmbAmount() != null && orderExports.get(i).getMerchantOrderRmb().getSourceRmbDeduct() != null) {
                cell = row.createCell(7);
                cell.setCellValue(orderExports.get(i).getRmbAmount()
                        .subtract(orderExports.get(i).getMerchantOrderRmb().getSourceRmbDeduct())
                        .subtract(orderExports.get(i).getMerchantOrderRmb().getSourceRmbBond())
                        .floatValue());
                cellNumFormatTwoDecimalPlaces.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
            }

            if (orderExports.get(i).getFinanceStatus() == 1) {
                cell = row.createCell(8);
                cell.setCellValue(orderExports.get(i).getFinanceTime());
                cellSetString.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                cell.setCellStyle(cellDateFormat);

                cell = row.createCell(9);
                cell.setCellValue("已清算");
                cell.setCellStyle(cellVerticalCenter);
            } else {
                cell = row.createCell(9);
                cell.setCellValue("未清算");
                cell.setCellStyle(cellVerticalCenter);
            }
            if (orderExports.get(i).getOrderChanges() != null && orderExports.get(i).getOrderChanges().size() != 0) {
                for (int j = 0; j < orderExports.get(i).getOrderChanges().size(); j++) {
                    if (j != 0) {
                        row = sheet.createRow(i + 1 + j + cellRangeAddressCount - 1);
                    }
                    cell = row.createCell(1);
                    cell.setCellValue(orderExports.get(i).getOrderChanges().get(j).getAmount().floatValue());
                    cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
                    cell = row.createCell(2);
                    cell.setCellValue(orderExports.get(i).getCurrency());
                    cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
                    cell = row.createCell(3);
                    cell.setCellValue(String.valueOf(orderExports.get(i).getOrderChanges().get(j).getRmbAmount()));
                    cell.setCellStyle(cellNumFormatTwoDecimalPlaces);
                    cell = row.createCell(4);
                    cell.setCellValue(orderExports.get(i).getOrderChanges().get(j).getType() == null ? "" :
                            exceptionStatus.get(String.valueOf(orderExports.get(i).getOrderChanges().get(j).getType())).getName());
                }
            }

            sheet = workbook.getSheetAt(3);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orderExports.get(i).getId()));
            cell.setCellStyle(cellSetString);

            cell = row.createCell(1);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getLastName() + " " + orderExports.get(i).getOrderDetail().getFirstName());

            cell = row.createCell(2);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getPhone());
            cell.setCellStyle(cellSetString);

            cell = row.createCell(3);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getAddress());

            cell = row.createCell(4);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getZip());
            cell.setCellStyle(cellSetString);

            cell = row.createCell(5);
            cell.setCellValue(orderExports.get(i).getOrderDetail().getEmail());
            cell.setCellStyle(cellSetString);
            if (orderExports.get(i).getCargoTrack() != null) {
                cell = row.createCell(6);
                cell.setCellValue(orderExports.get(i).getCargoTrack().getExpressName());

                cell = row.createCell(7);
                cell.setCellValue(orderExports.get(i).getCargoTrack().getExpressNo());

                cell = row.createCell(8);
                cell.setCellValue(String.valueOf(orderExports.get(i).getCargoTrack().getCreateTime()));
                cell.setCellStyle(cellDateFormat);
            }
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    public static void exportBondInfoToExcel(HttpServletResponse response, List<VoCoreOrder> orders, Map<String, SysConstant> bondStatus) throws IOException {
        String[] SHEET_TITLES = {"保证金信息"};
        String[] ROW_TITLES =
                {"平台订单号", "商户号", "域名", "商户订单号", "订单金额", "订单人民币金额", "银行金额", "交易时间",
                        "保证金金额", "保证金人民币金额", "释放时间", "释放状态"};
        // 新建excel
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/MM/dd HH:mm"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);
        for (int i = 0; i < ROW_TITLES.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(ROW_TITLES[i]);
            cell.setCellStyle(headerCellStyle);
            if (i == 0) {
                sheet.setColumnWidth(i, ROW_TITLES[i].getBytes().length * 256 + 1024 * 3);
            } else {
                sheet.setColumnWidth(i, ROW_TITLES[i].getBytes().length * 256 + 1024);
            }
        }
        for (int i = 0; i < orders.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);
            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(orders.get(i).getId()));
            cell.setCellStyle(cellSetString);

            cell = row.createCell(1);
            cell.setCellValue(orders.get(i).getMerId());

            cell = row.createCell(2);
            cell.setCellValue(orders.get(i).getSiteUrl());

            cell = row.createCell(3);
            cell.setCellValue(orders.get(i).getMerOrderId());

            cell = row.createCell(4);
            cell.setCellValue(orders.get(i).getCurrency() + " " + orders.get(i).getAmount());

            cell = row.createCell(5);
            cell.setCellValue("CNY " + orders.get(i).getRmbAmount());

            cell = row.createCell(6);
            cell.setCellValue(orders.get(i).getCurrency() + " " + orders.get(i).getChannelAmount());

            cell = row.createCell(7);
            cell.setCellValue(orders.get(i).getTradeTime());
            cell.setCellStyle(cellDateFormat);

            cell = row.createCell(8);
            cell.setCellValue(orders.get(i).getCurrency() + " " + orders.get(i).getBondAmount());

            cell = row.createCell(9);
            cell.setCellValue("CNY " + orders.get(i).getBondRmbAmount());

            if (orders.get(i).getBondTime() != null) {
                cell = row.createCell(10);
                cell.setCellValue(orders.get(i).getBondTime());
                cell.setCellStyle(cellDateFormat);
            }

            cell = row.createCell(11);
            cell.setCellValue(bondStatus.get(String.valueOf(orders.get(i).getBondStatus())).getName());
        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }


    public static void exportTransferlistToExcel(HttpServletResponse response, List<FinanceDrawing> drawingList) throws IOException {

        String[] SHEET_TITLES = {"划款查询"};
        String[] ROW_TITLES = {"提现流水号", "商户号", "商户名称", "提现金额", "划款金额", "实际划款金额","申请人","申请时间","审核人","审核时间","划款人","划款时间","状态","备注","复核描述","划款描述"};
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        Row row;
        Cell cell;
        // 字体样式，设置粗体
        Font headerFount = workbook.createFont();
        headerFount.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 单元格样式，设置字体样式
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerCellStyle.setFont(headerFount);
        // 单元格样式，格式化时间
        CellStyle cellDateFormat = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateFormat.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 数据格式
        DataFormat dataFormat = workbook.createDataFormat();
        // 文本
        CellStyle cellSetString = workbook.createCellStyle();
        cellSetString.setDataFormat(dataFormat.getFormat("@"));
        // 保留两位小数
        CellStyle cellNumFormatTwoDecimalPlaces = workbook.createCellStyle();
        cellNumFormatTwoDecimalPlaces.setDataFormat(dataFormat.getFormat("#,##0.00"));
        // 保留四位小数
        CellStyle cellNumFormatFourDecimalPlaces = workbook.createCellStyle();
        cellNumFormatFourDecimalPlaces.setDataFormat(dataFormat.getFormat("#,####0.0000"));
        // 垂直居中
        CellStyle cellVerticalCenter = workbook.createCellStyle();
        cellVerticalCenter.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        sheet = workbook.createSheet(SHEET_TITLES[0]);
        row = sheet.createRow(0);

        setSheetTitle(ROW_TITLES, sheet, row, headerCellStyle);
        for (int i = 0; i < drawingList.size(); i++) {
            sheet = workbook.getSheetAt(0);
            row = sheet.createRow(i + 1);

            cell = row.createCell(0);
            cell.setCellValue(String.valueOf(drawingList.get(i).getId()));

            cell = row.createCell(1);
            cell.setCellValue(drawingList.get(i).getRefId());

            cell = row.createCell(2);
            cell.setCellValue(drawingList.get(i).getMerName());

            cell = row.createCell(3);
            cell.setCellValue(drawingList.get(i).getAmount().toString());

            cell = row.createCell(4);
            cell.setCellValue(drawingList.get(i).getTransferAmount().toString());

            cell = row.createCell(5);
            cell.setCellValue(drawingList.get(i).getRealAmount() == null ? "" : drawingList.get(i).getRealAmount().toString());

            cell = row.createCell(6);
            cell.setCellValue(drawingList.get(i).getApplicant());

            cell = row.createCell(7);
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(drawingList.get(i).getApplicationTime()));

            cell = row.createCell(8);
            cell.setCellValue(drawingList.get(i).getAuditor());

            cell = row.createCell(9);
            cell.setCellValue(drawingList.get(i).getAuditTime() == null? "" : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(drawingList.get(i).getAuditTime()));

            cell = row.createCell(10);
            cell.setCellValue(drawingList.get(i).getTransfer());

            cell = row.createCell(11);
            cell.setCellValue(drawingList.get(i).getTransferTime() == null ? "" : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(drawingList.get(i).getTransferTime()));

            cell = row.createCell(12);
            switch (drawingList.get(i).getStatus()){
                case 0:
                    cell.setCellValue("无效");
                    break;
                case 1:
                    cell.setCellValue("待审核");
                    break;
                case 2:
                    cell.setCellValue("待划款");
                    break;
                case 3:
                    cell.setCellValue("已划款");
                    break;
                case 4:
                    cell.setCellValue("自动扣款");
                    break;
                case 5:
                    cell.setCellValue("待复核");
                    break;
                default:
                    cell.setCellValue("");

            }

            cell = row.createCell(13);
            cell.setCellValue(drawingList.get(i).getRemark());

            cell = row.createCell(14);
            cell.setCellValue(drawingList.get(i).getReviewRemark());

            cell = row.createCell(15);
            cell.setCellValue(drawingList.get(i).getTransferRemark());

        }
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    private static void setSheetTitle(String[] ROW_TITLES, Sheet sheet, Row row, CellStyle headerCellStyle) {
        Cell cell;
        for (int i = 0; i < ROW_TITLES.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(ROW_TITLES[i]);
            cell.setCellStyle(headerCellStyle);
            if (i == 0) {
                sheet.setColumnWidth(i, ROW_TITLES[i].getBytes().length * 256 + 1024 * 3);
            } else {
                sheet.setColumnWidth(i, ROW_TITLES[i].getBytes().length * 256 + 1024);
            }
        }
    }




}